/*
 * SubSonic - http://subsonicproject.com
 * 
 * The contents of this file are subject to the Mozilla Public
 * License Version 1.1 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a copy of
 * the License at http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an 
 * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 * implied. See the License for the specific language governing
 * rights and limitations under the License.
*/

using System.Collections.Generic;
using System.Text;

namespace SubSonic.QueryEngine
{
    /// <summary>
    /// Summary for the SqlServerParser class
    /// </summary>
    public class SqlServerParser : SqlParser
    {
        /// <summary>
        /// Selects the template.
        /// </summary>
        /// <returns></returns>
        public override string SelectTemplate()
        {
            string result =
                @"SELECT TOP #TOP# #COLUMNS# 
                            FROM #FROM# 
                            #JOINLIST# 
                            #WHERE# 
                            #ORDERBY#";
            return result;
        }

        /// <summary>
        /// Gets the select.
        /// </summary>
        /// <param name="topQualifier">The top qualifier.</param>
        /// <param name="columnList">The column list.</param>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public override string GetSelect(string topQualifier, string columnList, TableSchema.Table table)
        {
            string result = "SELECT TOP " + topQualifier + " " + columnList + " FROM " + GetQualifiedTable(table);
            return result;
        }

        /// <summary>
        /// Gets the select.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public override string GetSelect(TableSchema.Table table)
        {
            StringBuilder sb = new StringBuilder();
            foreach(TableSchema.TableColumn col in table.Columns)
                sb.Append(GetQualifiedColumn(col) + ",");
            string columnList = SubSonic.Sugar.Strings.Chop(sb.ToString());
            return GetSelect("100 PERCENT", columnList, table);
        }

        /// <summary>
        /// Gets the join.
        /// </summary>
        /// <param name="table1">The table1.</param>
        /// <param name="table2">The table2.</param>
        /// <returns></returns>
        public override string GetJoin(TableSchema.Table table1, TableSchema.Table table2)
        {
            return GetJoin(JoinType.Inner, table1, table1.PrimaryKey, table2, table2.PrimaryKey);
        }

        /// <summary>
        /// Gets the join.
        /// </summary>
        /// <param name="joinType">Type of the join.</param>
        /// <param name="table1">The table1.</param>
        /// <param name="table2">The table2.</param>
        /// <returns></returns>
        public override string GetJoin(JoinType joinType, TableSchema.Table table1, TableSchema.Table table2)
        {
            return GetJoin(joinType, table1, table1.PrimaryKey, table2, table2.PrimaryKey);
        }

        /// <summary>
        /// Gets the join.
        /// </summary>
        /// <param name="joinType">Type of the join.</param>
        /// <param name="table1">The table1.</param>
        /// <param name="column1">The column1.</param>
        /// <param name="table2">The table2.</param>
        /// <param name="column2">The column2.</param>
        /// <returns></returns>
        public override string GetJoin(JoinType joinType, TableSchema.Table table1, TableSchema.TableColumn column1, TableSchema.Table table2, TableSchema.TableColumn column2)
        {
            string joiner;
            string equality = SqlFragment.EQUAL_TO;
            switch(joinType)
            {
                case JoinType.Inner:
                    joiner = SqlJoin.INNER;
                    break;
                case JoinType.Outer:
                    joiner = SqlJoin.OUTER;
                    break;
                case JoinType.LeftInner:
                    joiner = SqlJoin.LEFT_INNER;
                    break;
                case JoinType.LeftOuter:
                    joiner = SqlJoin.LEFT_OUTER;
                    break;
                case JoinType.RightInner:
                    joiner = SqlJoin.RIGHT_INNER;
                    break;
                case JoinType.RightOuter:
                    joiner = SqlJoin.RIGHT_OUTER;
                    break;
                case JoinType.NotEqual:
                    joiner = SqlJoin.UNEQUAL;
                    equality = SqlFragment.NOT_EQUAL_TO;
                    break;
                case JoinType.Cross:
                    joiner = SqlJoin.CROSS;
                    break;
                default:
                    joiner = SqlJoin.INNER;
                    break;
            }

            StringBuilder sbResult = new StringBuilder(joiner);
            sbResult.Append(GetQualifiedTable(table1));
            sbResult.Append(SqlFragment.ON);
            sbResult.Append(GetQualifiedColumn(column1));
            sbResult.Append(equality);
            sbResult.Append(GetQualifiedColumn(column2));
            return sbResult.ToString();
        }

        /// <summary>
        /// Gets the comparison operator.
        /// </summary>
        /// <param name="comp">The comp.</param>
        /// <returns></returns>
        private static string GetComparisonOperator(Comparison comp)
        {
            string sOut;
            switch(comp)
            {
                case Comparison.Blank:
                    sOut = SqlComparison.BLANK;
                    break;
                case Comparison.GreaterThan:
                    sOut = SqlComparison.GREATER;
                    break;
                case Comparison.GreaterOrEquals:
                    sOut = SqlComparison.GREATER_OR_EQUAL;
                    break;
                case Comparison.LessThan:
                    sOut = SqlComparison.LESS;
                    break;
                case Comparison.LessOrEquals:
                    sOut = SqlComparison.LESS_OR_EQUAL;
                    break;
                case Comparison.Like:
                    sOut = SqlComparison.LIKE;
                    break;
                case Comparison.NotEquals:
                    sOut = SqlComparison.NOT_EQUAL;
                    break;
                case Comparison.NotLike:
                    sOut = SqlComparison.NOT_LIKE;
                    break;
                case Comparison.Is:
                    sOut = SqlComparison.IS;
                    break;
                case Comparison.IsNot:
                    sOut = SqlComparison.IS_NOT;
                    break;
                default:
                    sOut = SqlComparison.EQUAL;
                    break;
            }
            return sOut;
        }

        /// <summary>
        /// Gets the where.
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="comp">The comp.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public override string GetWhere(string columnName, Comparison comp, object value)
        {
            return GetConditionString(columnName, comp, value, SqlFragment.WHERE);
        }

        /// <summary>
        /// Gets the and.
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="comp">The comp.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public override string GetAnd(string columnName, Comparison comp, object value)
        {
            return GetConditionString(columnName, comp, value, SqlFragment.AND);
        }

        /// <summary>
        /// Gets the or.
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="comp">The comp.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public override string GetOr(string columnName, Comparison comp, object value)
        {
            return GetConditionString(columnName, comp, value, SqlFragment.OR);
        }

        /// <summary>
        /// Gets the condition string.
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="comp">The comp.</param>
        /// <param name="value">The value.</param>
        /// <param name="conditionString">The condition string.</param>
        /// <returns></returns>
        private static string GetConditionString(string columnName, Comparison comp, object value, string conditionString)
        {
            StringBuilder sbCondition = new StringBuilder(conditionString);
            sbCondition.Append(columnName);
            sbCondition.Append(GetComparisonOperator(comp));
            sbCondition.Append(value.ToString());
            return sbCondition.ToString();
        }

        /// <summary>
        /// Gets the insert select.
        /// </summary>
        /// <param name="to">To.</param>
        /// <param name="listTo">The list to.</param>
        /// <param name="from">From.</param>
        /// <param name="listFrom">The list from.</param>
        /// <returns></returns>
        public override string GetInsertSelect(TableSchema.Table to, string listTo, TableSchema.Table from, string listFrom)
        {
            string insertFormat = @"INSERT INTO {0} ({1})
                                  SELECT {2} FROM {3}; ";
            string result = string.Format(insertFormat, GetQualifiedTable(to), listTo, GetQualifiedTable(from), listFrom);
            return result;
        }

        /// <summary>
        /// Gets the insert values.
        /// </summary>
        /// <param name="to">To.</param>
        /// <param name="columns">The columns.</param>
        /// <returns></returns>
        public override string GetInsertValues(TableSchema.Table to, List<string> columns)
        {
            string insertFormat = @"INSERT INTO {0} ({1})
                                  VALUES({2});";

            string cols = Sugar.Strings.ToDelimitedList(columns);
            StringBuilder sbParams = new StringBuilder();
            foreach(string s in columns)
                sbParams.AppendFormat("@{0},", s);
            string paramList = sbParams.ToString();
            paramList = Sugar.Strings.Chop(paramList);

            string result = string.Format(insertFormat, GetQualifiedTable(to), cols, paramList);

            return result;
        }

        /// <summary>
        /// Gets the update.
        /// </summary>
        /// <param name="to">To.</param>
        /// <param name="columns">The columns.</param>
        /// <returns></returns>
        public override string GetUpdate(TableSchema.Table to, List<string> columns)
        {
            string updateFormat = "UPDATE {0} {1} ";
            string setFormat = "SET {0} = @{0},";
            StringBuilder sb = new StringBuilder();
            foreach(string s in columns)
                sb.AppendFormat(setFormat, s);
            string setStatement = Sugar.Strings.Chop(sb.ToString());
            string result = string.Format(updateFormat, GetQualifiedTable(to), setStatement);
            return result;
        }

        /// <summary>
        /// Gets the delete.
        /// </summary>
        /// <param name="tbl">The TBL.</param>
        /// <returns></returns>
        public override string GetDelete(TableSchema.Table tbl)
        {
            return "DELETE FROM " + GetQualifiedTable(tbl);
        }

        /// <summary>
        /// Gets the qualified table.
        /// </summary>
        /// <param name="tbl">The TBL.</param>
        /// <returns></returns>
        public static string GetQualifiedTable(TableSchema.Table tbl)
        {
            string resultFormat = "[{0}].[{1}]";
            return string.Format(resultFormat, tbl.SchemaName, tbl.Name);
        }

        /// <summary>
        /// Gets the qualified column.
        /// </summary>
        /// <param name="col">The col.</param>
        /// <returns></returns>
        public static string GetQualifiedColumn(TableSchema.TableColumn col)
        {
            string resultFormat = "[{0}].[{1}].[{2}]";
            return string.Format(resultFormat, col.Table.SchemaName, col.Table.Name, col.ColumnName);
        }
    }
}
